
/******************************************************************************
 * Copyright Cambridge Silicon Radio Limited 2014 - 2015.
 ******************************************************************************/

package com.ihomey.linkuphome.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.ihomey.linkuphome.entities.Category;
import com.ihomey.linkuphome.entities.GroupDevice;
import com.ihomey.linkuphome.entities.LightState;
import com.ihomey.linkuphome.entities.Setting;
import com.ihomey.linkuphome.entities.SingleDevice;

import java.util.ArrayList;
import java.util.List;

public class DataBaseDataSource {

    // Log Tag
    private String TAG = "DataBaseDataSource";

    // Database fields
    private SQLiteDatabase db;
    private MeshSQLHelper dbHelper;

    public DataBaseDataSource(Context context) {
        dbHelper = new MeshSQLHelper(context);
    }

    /**
     * Open the database
     *
     * @throws SQLException
     */
    public void open() throws SQLException {
        db = dbHelper.getWritableDatabase();
    }

    /**
     * Close the database
     */
    public void close() {
        dbHelper.close();
    }

    /**
     * Get single setting entry by isAdded.
     *
     * @param isAdded
     * @return
     */
    public List<Category> getCategories(boolean isAdded) {
        open();
        ArrayList<Category> categories = new ArrayList<Category>();
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_CATEGORIES
                + " WHERE " + MeshSQLHelper.CATEGORIES_COLUMN_ADDED + " = " + (isAdded ? 1 : 0);
        Cursor c = db.rawQuery(selectQuery, null);
        while (c.moveToNext()) {
            Category category = new Category();
            category.setId(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.CATEGORIES_COLUMN_ID)));
            category.setType(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.CATEGORIES_COLUMN_TYPE)));
            category.setAdded(c.getInt(c.getColumnIndex(MeshSQLHelper.CATEGORIES_COLUMN_ADDED)) == 0 ? false : true);
            categories.add(category);
        }
        close();
        return categories;
    }


    public void updateCategory(int categoryId, boolean added) {
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.CATEGORIES_COLUMN_ADDED, added);
        db.update(MeshSQLHelper.TABLE_CATEGORIES, values,
                MeshSQLHelper.CATEGORIES_COLUMN_ID + "=" + categoryId, null);
        close();

    }

    /**
     * Create a setting entry or update if it already exists.
     *
     * @param setting
     * @return
     */
    public Setting createSetting(Setting setting) {
        Log.d(TAG, "Creating or updating (if it already exists) setting values.");
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.SETTINGS_COLUMN_KEY, setting.getNetworkKey());
        values.put(MeshSQLHelper.SETTINGS_COLUMN_NEXT_DEVICE_INDEX, setting.getLastDeviceIndex());
        values.put(MeshSQLHelper.SETTINGS_COLUMN_NEXT_GROUP_INDEX, setting.getLastGroupIndex());
        values.put(MeshSQLHelper.SETTINGS_COLUMN_AUTH_REQUIRED, setting.isAuthRequired());
        values.put(MeshSQLHelper.SETTINGS_COLUMN_TTL, setting.getTTL());

        long id;
        if (setting.getId() != Setting.UKNOWN_ID) {
            values.put(MeshSQLHelper.SETTINGS_COLUMN_ID, setting.getId());
            id = db.replace(MeshSQLHelper.TABLE_SETTINGS, null, values);
        } else {
            id = db.insert(MeshSQLHelper.TABLE_SETTINGS, null, values);
        }

        close();

        if (id == -1) {
            // error, return null;
            return null;
        } else {
            setting.setId((int) id);
            return setting;
        }
    }

    /**
     * Get single setting entry by id.
     *
     * @param id
     * @return
     */
    public Setting getSetting(int id) {
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_SETTINGS
                + " WHERE " + MeshSQLHelper.SETTINGS_COLUMN_ID + " = " + id;

        open();
        Cursor c = db.rawQuery(selectQuery, null);

        if (c != null && c.moveToFirst()) {
            Setting setting = new Setting();
            setting.setId(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.SETTINGS_COLUMN_ID)));
            setting.setNetworkKey(c.getString(c
                    .getColumnIndex(MeshSQLHelper.SETTINGS_COLUMN_KEY)));
            setting.setLastDeviceIndex(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.SETTINGS_COLUMN_NEXT_DEVICE_INDEX)));
            setting.setLastGroupIndex(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.SETTINGS_COLUMN_NEXT_GROUP_INDEX)));
            setting.setAuthRequired((c.getInt(c
                    .getColumnIndex(MeshSQLHelper.SETTINGS_COLUMN_AUTH_REQUIRED)) > 0));
            setting.setTTL(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.SETTINGS_COLUMN_TTL)));
            close();
            return setting;
        } else {
            close();
            return null;
        }
    }

    /**
     * Get single setting entry by id.
     *
     * @param deviceId
     * @param deviceType
     * @return
     */
    public SingleDevice getSingleDevice(int deviceId, int deviceType) {
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_DEVICES
                + " WHERE " + MeshSQLHelper.DEVICES_COLUMN_ID + " = " + deviceId + " and " + MeshSQLHelper.DEVICES_COLUMN_TYPE + " = " + deviceType;

        open();
        Cursor deviceCursor = db.rawQuery(selectQuery, null);

        if (deviceCursor != null && deviceCursor.moveToFirst()) {
            SingleDevice device = new SingleDevice(
                    deviceCursor.getInt(deviceCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_ID)),
                    deviceCursor.getInt(deviceCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_TYPE)),
                    deviceCursor.getString(deviceCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_NAME)),
                    deviceCursor.getInt(deviceCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_HASH)),
                    deviceCursor.getLong(deviceCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_LOW)),
                    deviceCursor.getLong(deviceCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_HIGH)));
            device.setMinimumSupportedGroups(deviceCursor.getInt(deviceCursor
                    .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_GROUPS_SUPPORTED)));

            String query = "SELECT " + MeshSQLHelper.MODELS_COLUMN_GROUP_ID
                    + " FROM " + MeshSQLHelper.TABLE_MODELS + " WHERE "
                    + MeshSQLHelper.MODELS_COLUMN_DEVICE_ID + " ='"
                    + device.getDeviceId() + "' and " + MeshSQLHelper.MODELS_COLUMN_TYPE + "=" + deviceType;
            Cursor groupsCursor = db.rawQuery(query, null);

            for (int i = 0; groupsCursor.moveToNext(); i++) {
                device.setGroupId(i, groupsCursor.getInt(groupsCursor
                        .getColumnIndex(MeshSQLHelper.MODELS_COLUMN_GROUP_ID)));
            }
            return device;
        } else {
            close();
            return null;
        }
    }


    public GroupDevice getGroupDevice(int deviceId, int deviceType) {
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_GROUPS
                + " WHERE " + MeshSQLHelper.GROUPS_COLUMN_ID + " = " + deviceId + " and " + MeshSQLHelper.GROUPS_COLUMN_TYPE + " = " + deviceType;
        open();
        Cursor groupCursor = db.rawQuery(selectQuery, null);
        if (groupCursor != null && groupCursor.moveToFirst()) {
            GroupDevice group = new GroupDevice(
                    groupCursor.getInt(groupCursor
                            .getColumnIndex(MeshSQLHelper.GROUPS_COLUMN_ID)),
                    groupCursor.getInt(groupCursor
                            .getColumnIndex(MeshSQLHelper.GROUPS_COLUMN_TYPE)),
                    groupCursor.getString(groupCursor
                            .getColumnIndex(MeshSQLHelper.GROUPS_COLUMN_NAME)));
            return group;
        } else {
            close();
            return null;
        }
    }

    /**
     * Get the list of SingleDevices stored in the database.
     *
     * @param deviceType
     * @return
     */
    public ArrayList<SingleDevice> getAllSingleDevices(int deviceType) {
        open();
        db.beginTransaction();
        ArrayList<SingleDevice> devices = new ArrayList<SingleDevice>();
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_DEVICES + " WHERE " + MeshSQLHelper.DEVICES_COLUMN_TYPE + "=" + deviceType;
        Cursor devicesCursor = db.rawQuery(selectQuery, null);
        while (devicesCursor.moveToNext()) {
            SingleDevice device = new SingleDevice(
                    devicesCursor.getInt(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_ID)),
                    devicesCursor.getInt(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_TYPE)),
                    devicesCursor.getString(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_NAME)),
                    devicesCursor.getInt(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_HASH)),
                    devicesCursor.getLong(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_LOW)),
                    devicesCursor.getLong(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_HIGH)));
            device.setMinimumSupportedGroups(devicesCursor.getInt(devicesCursor
                    .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_GROUPS_SUPPORTED)));

            String query = "SELECT " + MeshSQLHelper.MODELS_COLUMN_GROUP_ID
                    + " FROM " + MeshSQLHelper.TABLE_MODELS + " WHERE "
                    + MeshSQLHelper.MODELS_COLUMN_DEVICE_ID + " ='"
                    + device.getDeviceId() + "' and " + MeshSQLHelper.MODELS_COLUMN_TYPE + "=" + deviceType;
            Cursor groupsCursor = db.rawQuery(query, null);

            for (int i = 0; groupsCursor.moveToNext(); i++) {
                device.setGroupId(i, groupsCursor.getInt(groupsCursor
                        .getColumnIndex(MeshSQLHelper.MODELS_COLUMN_GROUP_ID)));
            }
            devices.add(device);
        }
        db.endTransaction();
        close();

        return devices;
    }

    public ArrayList<SingleDevice> getAllSingleDevices() {
        open();
        db.beginTransaction();
        ArrayList<SingleDevice> devices = new ArrayList<SingleDevice>();
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_DEVICES;
        Cursor devicesCursor = db.rawQuery(selectQuery, null);
        while (devicesCursor.moveToNext()) {
            SingleDevice device = new SingleDevice(
                    devicesCursor.getInt(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_ID)),
                    devicesCursor.getInt(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_TYPE)),
                    devicesCursor.getString(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_NAME)),
                    devicesCursor.getInt(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_HASH)),
                    devicesCursor.getLong(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_LOW)),
                    devicesCursor.getLong(devicesCursor
                            .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_HIGH)));
            device.setMinimumSupportedGroups(devicesCursor.getInt(devicesCursor
                    .getColumnIndex(MeshSQLHelper.DEVICES_COLUMN_GROUPS_SUPPORTED)));

            String query = "SELECT " + MeshSQLHelper.MODELS_COLUMN_GROUP_ID
                    + " FROM " + MeshSQLHelper.TABLE_MODELS + " WHERE "
                    + MeshSQLHelper.MODELS_COLUMN_DEVICE_ID + " ='"
                    + device.getDeviceId() + "'";
            Cursor groupsCursor = db.rawQuery(query, null);

            for (int i = 0; groupsCursor.moveToNext(); i++) {
                device.setGroupId(i, groupsCursor.getInt(groupsCursor
                        .getColumnIndex(MeshSQLHelper.MODELS_COLUMN_GROUP_ID)));
            }
            devices.add(device);
        }
        db.endTransaction();
        close();

        return devices;
    }

    /**
     * Get single setting entry by id.
     *
     * @param deviceId
     * @return
     */
    public LightState getLightState(int deviceId, int deviceType) {
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_LIGHT_STATE
                + " WHERE " + MeshSQLHelper.LIGHT_STATE_COLUMN_DEVICE_ID + " = " + deviceId + " and " + MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE + " = " + deviceType;
        Log.d("aa",selectQuery);
        open();
        Cursor c = db.rawQuery(selectQuery, null);

        if (c != null && c.moveToFirst()) {
            LightState lightState = new LightState();
            lightState.setId(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_DEVICE_ID)));
            lightState.setType(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE)));
            lightState.setIsOn(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON)));
            lightState.setIsLight(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_LIGHT)));
            lightState.setChangeMode(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_CHANGE_MODE)));
            lightState.setColorPosition(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_COLOR_POSITION)));
            lightState.setBrightness(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_BRIGHTNESS)));
            lightState.setOpenTimer(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_OPEN_TIMER)));
            lightState.setCloseTimer(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_CLOSE_TIMER)));
            lightState.setIsOnOpenTimer(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON_OPEN_TIMER)));
            lightState.setIsOnCloseTimer(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON_CLOSE_TIMER)));
            lightState.setSceneMode(c.getInt(c
                    .getColumnIndex(MeshSQLHelper.LIGHT_STATE_COLUMN_SCENE_MODE)));

            close();
            return lightState;
        } else {
            close();
            return null;
        }
    }

    public ArrayList<GroupDevice> getAllGroupDevices() {
        open();
        ArrayList<GroupDevice> groups = new ArrayList<GroupDevice>();
        String selectQuery = "SELECT  * FROM " + MeshSQLHelper.TABLE_GROUPS ;

        Cursor groupsCursor = db.rawQuery(selectQuery, null);
        while (groupsCursor.moveToNext()) {
            GroupDevice group = new GroupDevice(
                    groupsCursor.getInt(groupsCursor
                            .getColumnIndex(MeshSQLHelper.GROUPS_COLUMN_ID)),
                    groupsCursor.getInt(groupsCursor
                            .getColumnIndex(MeshSQLHelper.GROUPS_COLUMN_TYPE)),
                    groupsCursor.getString(groupsCursor
                            .getColumnIndex(MeshSQLHelper.GROUPS_COLUMN_NAME)));
            groups.add(group);
        }
        close();
        return groups;
    }


    /**
     * Create a GroupDevice or update if it already exists in the database.
     *
     * @param group
     * @param settingsID
     * @return
     */
    public GroupDevice createOrUpdateGroup(GroupDevice group) {
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.GROUPS_COLUMN_ID, group.getDeviceId());
        values.put(MeshSQLHelper.GROUPS_COLUMN_TYPE, group.getDeviceType());
        values.put(MeshSQLHelper.GROUPS_COLUMN_NAME, group.getName());

        // insert row and close db
        long id = db.replace(MeshSQLHelper.TABLE_GROUPS, null, values);
        close();

        if (id == -1) {
            // error, return null;
            return null;
        } else {
            group.setDeviceId((int) id);
            return group;
        }
    }


    /**
     * Create a GroupDevice or update if it already exists in the database.
     *
     * @param group
     * @param settingsID
     * @return
     */
    public void createOrUpdateGroup(int groupId,int type,String name) {
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.GROUPS_COLUMN_ID, groupId);
        values.put(MeshSQLHelper.GROUPS_COLUMN_TYPE, type);
        values.put(MeshSQLHelper.GROUPS_COLUMN_NAME, name);

        // insert row and close db
        long id = db.replace(MeshSQLHelper.TABLE_GROUPS, null, values);
        close();

//        if (id == -1) {
//            // error, return null;
//            return null;
//        } else {
//            group.setDeviceId((int) id);
//            return group;
//        }
    }

    /**
     * Create a GroupDevice or update if it already exists in the database.
     *
     * @param lightState
     * @return
     */
    public LightState createOrUpdateLightState(LightState lightState) {
        open();
        ContentValues values = new ContentValues();
        Log.d("aa","createOrUpdateLightState---"+lightState.getId());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_DEVICE_ID, lightState.getId());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE, lightState.getType());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON, lightState.getIsOn());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_LIGHT, lightState.getIsLight());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_CHANGE_MODE, lightState.getChangeMode());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_COLOR_POSITION, lightState.getColorPosition());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_BRIGHTNESS, lightState.getBrightness());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_SCENE_MODE, lightState.getSceneMode());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_OPEN_TIMER, lightState.getOpenTimer());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_CLOSE_TIMER, lightState.getCloseTimer());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON_OPEN_TIMER, lightState.getIsOnOpenTimer());
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON_CLOSE_TIMER, lightState.getIsOnCloseTimer());

        // insert row and close db
        long id = db.replace(MeshSQLHelper.TABLE_LIGHT_STATE, null, values);
        close();

        if (id == -1) {
            // error, return null;
            return null;
        } else {
            lightState.setId((int) id);
            return lightState;
        }
    }

    public void createLightState(int deviceId,int type) {
        open();
        ContentValues values = new ContentValues();
        Log.d("aa","createLightState---"+deviceId);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_DEVICE_ID, deviceId);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE, type);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON, 1);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_LIGHT, 0);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_CHANGE_MODE, -1);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_COLOR_POSITION, 0);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_BRIGHTNESS, 20);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_SCENE_MODE, 0);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_OPEN_TIMER, 0);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_CLOSE_TIMER, 0);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON_OPEN_TIMER, 0);
        values.put(MeshSQLHelper.LIGHT_STATE_COLUMN_IS_ON_CLOSE_TIMER, 0);

        // insert row and close db
        long id = db.replace(MeshSQLHelper.TABLE_LIGHT_STATE, null, values);
        close();
    }

    /**
     * Create a SingleDevice or update if it already exists in the database.
     *
     * @param device
     * @param settingsID
     * @return
     */
    public boolean createOrUpdateSingleDevice(SingleDevice device) {
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.DEVICES_COLUMN_ID, device.getDeviceId());
        values.put(MeshSQLHelper.DEVICES_COLUMN_NAME, device.getName());
        values.put(MeshSQLHelper.DEVICES_COLUMN_TYPE, device.getDeviceType());
        values.put(MeshSQLHelper.DEVICES_COLUMN_GROUPS_SUPPORTED,
                device.getMinimumSupportedGroups());
        values.put(MeshSQLHelper.DEVICES_COLUMN_HASH, device.getUuidHash());
        values.put(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_LOW,
                device.getModelSupportBitmapLow());
        values.put(MeshSQLHelper.DEVICES_COLUMN_MODELSUPPORT_HIGH,
                device.getModelSupportBitmapHigh());

        // insert row and close db
        long id = db.replace(MeshSQLHelper.TABLE_DEVICES, null, values);
        close();

        if (id == -1) {
            // error, return null;
            close();
            return false;
        } else {
            removeAllModels(device.getDeviceId(),device.getDeviceType());
            for (int i = 0; i < device.getGroupMembership().size(); i++) {
                createOrUpdateModel(device.getDeviceId(), device
                        .getGroupMembership().get(i).intValue(), device.getDeviceType());
            }
            close();
            return true;
        }
    }

    /**
     * Update the device name of an existing device of the data base.
     *
     * @param deviceId
     * @param deviceType
     * @param name
     */
    public void updateDeviceName(int deviceId, int deviceType, String name) {
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.DEVICES_COLUMN_NAME, name);

        db.update(MeshSQLHelper.TABLE_DEVICES, values,
                MeshSQLHelper.DEVICES_COLUMN_ID + "=" + deviceId + " and " + MeshSQLHelper.DEVICES_COLUMN_TYPE + "=" + deviceType, null);
        close();

    }

    /**
     * Update the group name of an existing device of the group base.
     *
     * @param deviceId
     * @param deviceType
     * @param name
     */
    public void updateGroupName(int deviceId, int deviceType, String name) {
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.DEVICES_COLUMN_NAME, name);

        db.update(MeshSQLHelper.TABLE_GROUPS, values,
                MeshSQLHelper.DEVICES_COLUMN_ID + "=" + deviceId + " and " + MeshSQLHelper.GROUPS_COLUMN_TYPE + "=" + deviceType, null);
        close();

    }

    /**
     * Remove all the models that a device has.
     *
     * @param deviceType
     */
    public void removeAllModels(int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_MODELS,
                MeshSQLHelper.MODELS_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }

    /**
     * Remove all the models that a device has.
     * @param deviceId
     * @param deviceType
     */
    public void removeAllModels(int deviceId,int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_MODELS,MeshSQLHelper.MODELS_COLUMN_DEVICE_ID + "=" + deviceId+" and "+
                MeshSQLHelper.MODELS_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }

    /**
     * Remove a GroupDevice from the database by device id.
     *
     * @param groupId
     * @param deviceId
     * @param deviceType
     */
    public void removeModel(int groupId,int deviceId,int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_MODELS, MeshSQLHelper.MODELS_COLUMN_GROUP_ID
                + "=" + groupId + " and " + MeshSQLHelper.MODELS_COLUMN_DEVICE_ID+"="+deviceId+ " and " + MeshSQLHelper.GROUPS_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }

    public void removeLightState(int deviceId,int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_LIGHT_STATE, MeshSQLHelper.LIGHT_STATE_COLUMN_DEVICE_ID
                + "=" + deviceId + " and " + MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE+ "=" + deviceType, null);
        close();
    }

    /**
     * Remove all the models that a device has.
     *
     * @param deviceType
     */
    public void removeAllSingleDevices(int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_DEVICES,
                MeshSQLHelper.DEVICES_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }


    /**
     * Remove all the models that a device has.
     *
     * @param deviceType
     */
    public void removeAllGroups(int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_GROUPS,
                MeshSQLHelper.GROUPS_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }

    /**
     * Remove all the models that a device has.
     *
     * @param deviceType
     */
    public void removeAllLightState(int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_LIGHT_STATE,
                MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }

    /**
     * This method deletes the content of the settings', groups', devices' and models' tables.
     */
    public void cleanDatabase() {
        open();
        db.delete(MeshSQLHelper.TABLE_SETTINGS, null, null);
        db.delete(MeshSQLHelper.TABLE_GROUPS, null, null);
        db.delete(MeshSQLHelper.TABLE_DEVICES, null, null);
        db.delete(MeshSQLHelper.TABLE_MODELS, null, null);
        close();
    }

    /**
     * Create a model or update if it already exists in the database.
     *
     * @param deviceID
     * @param groupID
     */
    public void createOrUpdateModel(int deviceID, int groupID, int type) {
        open();
        ContentValues values = new ContentValues();
        values.put(MeshSQLHelper.MODELS_COLUMN_DEVICE_ID, deviceID);
        values.put(MeshSQLHelper.MODELS_COLUMN_GROUP_ID, groupID);
        values.put(MeshSQLHelper.MODELS_COLUMN_TYPE, type);

        // insert row and close db
        long id = db.replace(MeshSQLHelper.TABLE_MODELS, null, values);

        close();
    }

    /**
     * Remove a SingleDevice from the database by device id.
     *
     * @param deviceId
     * @param deviceType
     */
    public void removeSingleDevice(int deviceId, int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_DEVICES, MeshSQLHelper.DEVICES_COLUMN_ID
                + "=" + deviceId + " and " + MeshSQLHelper.DEVICES_COLUMN_TYPE + "=" + deviceType, null);
        db.delete(MeshSQLHelper.TABLE_MODELS, MeshSQLHelper.MODELS_COLUMN_DEVICE_ID
                + "=" + deviceId + " and " + MeshSQLHelper.MODELS_COLUMN_TYPE + "=" + deviceType, null);
        db.delete(MeshSQLHelper.TABLE_LIGHT_STATE, MeshSQLHelper.LIGHT_STATE_COLUMN_DEVICE_ID
                + "=" + deviceId + " and " + MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }

    /**
     * Remove a GroupDevice from the database by device id.
     *
     * @param groupId
     * @param deviceType
     */
    public void removeGroup(int groupId, int deviceType) {
        open();
        db.delete(MeshSQLHelper.TABLE_GROUPS, MeshSQLHelper.GROUPS_COLUMN_ID
                + "=" + groupId + " and " + MeshSQLHelper.GROUPS_COLUMN_TYPE + "=" + deviceType, null);
        db.delete(MeshSQLHelper.TABLE_LIGHT_STATE, MeshSQLHelper.LIGHT_STATE_COLUMN_DEVICE_ID
                + "=" + groupId + " and " + MeshSQLHelper.LIGHT_STATE_COLUMN_TYPE + "=" + deviceType, null);
        close();
    }

}
